package chapter07;

import java.sql.*;

public class Test06_goods {
    private static final String url = "jdbc:mysql://localhost:3306/" +
            "goodsManagement?serverTimezone=GMT%2B8&useSSL=false";;
    private static final String username = "root";
    private static final String pwd = "root";
    private static Connection con = null;
    private static PreparedStatement pstm = null;
    private static ResultSet rs = null;

    public static void main(String[] args) {
        try {
            Test06_goods.queryData();
        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            closeDB();
        }
        try {
            int i = Test06_goods.add("三星手机",5000,"韩国");
            System.out.println("成功的插入了"+i+"条数据");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeDB();
        }
        try {
            int i = Test06_goods.update(20, "OPPO");
            System.out.println("成功的修改了"+i+"条数据");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeDB();
        }
        try {
            int i = Test06_goods.delete(20);
            System.out.println("成功的删除了"+i+"条数据");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeDB();
        }
        try{
            Test06_goods.queryH("手机");
        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            closeDB();
        }
        try{
            Test06_goods.queryH("手机",1000,5000);
        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            closeDB();
        }
    }
    //数据库连接函数
    public static boolean getCon() throws ClassNotFoundException,
            SQLException {
        //注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        con = DriverManager.getConnection(url,username,pwd);
        return true;
    }
    //查询函数
    public static void queryData() throws SQLException,
            ClassNotFoundException {
        getCon();
        String sql = "select * from goods";
        pstm = con.prepareStatement(sql);
        //执行查询
        rs = pstm.executeQuery();
        //处理数据
        while(rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double price = rs.getDouble("price");
            String addre = rs.getString("addre");
            System.out.println(id+":"+name+":"+price+":"+addre);
        }
        //进行资源回收
        closeDB();
    }
    //模糊查询
    public static void queryH(String name) throws SQLException,
            ClassNotFoundException {
        getCon();
        String sql = "select * from goods where name like ?";
        //执行sql
        pstm = con.prepareStatement(sql);
        pstm.setString(1,"%"+name+"%");
        rs = pstm.executeQuery();
        //处理结果
        while(rs.next()){
            int id = rs.getInt("id");
            String name1 = rs.getString("name");
            double price = rs.getDouble("price");
            String addre = rs.getString("addre");
            System.out.println(id+":"+name1+":"+price+":"+addre);
        }
        closeDB();
    }
    public static void queryH(String name,double min,double max)
            throws SQLException, ClassNotFoundException {
        getCon();
        String sql = "SELECT * FROM goods " +
                "WHERE price BETWEEN ? AND ? AND NAME LIKE ?";
        //执行sql
        pstm = con.prepareStatement(sql);
        pstm.setDouble(1,min);
        pstm.setDouble(2,max);
        pstm.setString(3,"%"+name+"%");
        rs = pstm.executeQuery();
        while(rs.next()){
            int id = rs.getInt("id");
            String name1 = rs.getString("name");
            double price = rs.getDouble("price");
            String addre = rs.getString("addre");
            System.out.println(id+":"+name1+":"+price+":"+addre);
        }
        closeDB();
    }
    //新增函数
    public static int add(String name,double price,String addre)
            throws SQLException,
            ClassNotFoundException {
        getCon();
        String sql = "insert into goods values(NULL,?,?,?)";
        //执行sql
        pstm = con.prepareStatement(sql);
        //进行占位赋值
        pstm.setString(1,name);
        pstm.setDouble(2,price);
        pstm.setString(3,addre);
        //插入数据
        int i = pstm.executeUpdate();
        //回收资源
        closeDB();
        return i;
    }
    //修改函数
    public static int update(int id,String name) throws SQLException,
            ClassNotFoundException {
        getCon();
        String sql = "update goods set name = ? where id = ?";
        //执行sql语句
        pstm = con.prepareStatement(sql);
        pstm.setString(1,name);
        pstm.setInt(2,id);
        int i = pstm.executeUpdate();
        //回收资源
        closeDB();
        return i;
    }
    //删除函数
    public static int delete(int id) throws SQLException,
            ClassNotFoundException {
        getCon();
        String sql = "delete from goods where id=?";
        pstm = con.prepareStatement(sql);
        pstm.setInt(1,id);
        int i = pstm.executeUpdate();
        //回收资源
        closeDB();
        return i;
    }
    public static void closeDB(){
        try {
            if(rs!=null){
                rs.close();
            }
            if(pstm!=null){
                pstm.close();
            }
            if(con!=null){
                con.close();
            }
        }catch (Exception e){
            System.out.println(e.getMessage());
        }

    }
}
